Synopsis: SQL Injection

In March 2010, serial computer hacker Albert Gonzalez was convicted for his role in the largest identity theft in history. He acquired an estimated 130 million credit and debit card numbers by hacking into ATM machines and payment systems of several major retail store chains and the credit card processing companies that served them.

Gonzales broke the previous record, which he also held, for stealing 45.6 million credit and debit card numbers in 2006. He performed that earlier crime by exploiting vulnerable wireless networks.

How did Gonzalez nearly triple his own record? We imagine a daring plot from a James Bond movie, with black-clad agents rappelling down elevator shafts, using supercomputers to crack state-of-the-art encrypted passwords, or sabotaging electrical power to an entire city.

The indictment describes a more mundane reality. Gonzalez exploited a vulnerability that is one of the most common security weaknesses on the Internet. He was able to use an attack technique called SQL Injection to gain privileged access to upload files to the corporate victims’ servers. The indictment tates that after Gonzalez and his co-conspirators gained this access:

…they would install “sniffer” programs that would capture credit and debit card numbers, corresponding Card Data, and other information on a real-time basis as the information moved through the Corporate Victims’ credit and debit card processing networks, and then periodically transmit that information to the co-conspirators.

The retailers whose websites Gonzalez attacked say that they’ve made changes to correct these security holes. However, they’ve plugged only one hole, while new web applications are created every day that contain other holes. SQL Injection attacks remain an easy target for hackers because software developers don’t understand the nature of the vulnerability or how to write code to prevent it.

Objective: Write dynamic SQL queries#

SQL is intended to be used in concert with application code. When we build SQL queries as strings and combine application variables into the string, it is commonly called dynamic SQL.

<?php 
＄sql = "SELECT * FROM Bugs WHERE bug_id = ＄bug_id";
＄stmt = ＄pdo->query(＄sql);
?>

This simple example shows interpolating a PHP variable into a string. We intend that ＄bug_id is an integer so that by the time the database receives the query, the value of ＄bug_id is part of the query.

Dynamic SQL queries are a natural way to get the most out of a database. When we use application data to specify how we want to query a database, we’re using SQL as a two-way language. Our application is having a kind of dialogue with the database.

However, it’s not too hard to make our software do tasks that we want it to do — the harder challenge is making our software secure so that it doesn’t allow actions that we don’t want it to do. Software defects resulting from SQL Injection are failures to satisfy the latter.

Legitimate uses of the antipattern#

This antipattern is different from most of the other antipatterns in this course. There aren’t any legitimate reasons for allowing our application to have a security vulnerability because of SQL Injection. It’s our responsibility as software developers to write code defensively and to help our peers to do so as well. Software is only as secure as its weakest link — we need to make sure we’re not responsible for that weakest link!